from string import Template
from tack.lib import json_app
from util import sql_string, sql_int


class Sql:
    update_commitment_leg = """
    UPDATE tmpo_commitment_leg
    SET checkin = $checkin,
        start = $start,
        finish = $finish,
        location = $location,
        destination = $destination,
        passengers = $passengers,
        cargo = $cargo,
        comment = $comment
    WHERE commitment_leg_id = $commitment_leg_id
    """       


    delete_drivers = """
    DELETE FROM tmpo_commitment_leg_driver
    WHERE commitment_leg_id = $commitment_leg_id
    """


    delete_vehicles = """
    DELETE FROM tmpo_commitment_leg_vehicle
    WHERE commitment_leg_id = $commitment_leg_id
    """


    insert_driver = """
    INSERT INTO tmpo_commitment_leg_driver (commitment_leg_id, driver_id)
    VALUES ($commitment_leg_id, $driver_id)
    """


    insert_vehicle = """
    INSERT INTO tmpo_commitment_leg_vehicle (commitment_leg_id, vehicle_id)
    VALUES ($commitment_leg_id, $vehicle_id)
    """


def update_leg(environ, start_response):
    commitment_leg_id = environ['dispatcher.groups']['commitment_leg_id']
    form = environ['form']
    checkin = sql_string(form['checkin'])
    start = sql_string(form['start'])
    finish = sql_string(form['finish'])
    location = sql_string(form['location'])
    destination = sql_string(form['destination'])
    passengers = sql_int(form['passengers'])
    cargo = sql_string(form['cargo'])
    comment = sql_string(form['comment'])
    cursor = environ['connection'].cursor()
    sql = Template(Sql.update_commitment_leg).substitute(locals())
    cursor.execute(sql)
    environ['connection'].commit()
    sql = Template(Sql.delete_drivers).substitute(locals())
    cursor.execute(sql)
    environ['connection'].commit()
    sql = Template(Sql.delete_vehicles).substitute(locals())
    cursor.execute(sql)
    environ['connection'].commit()
    for driver_id in form['drivers']:
        if driver_id != '0':
            sql = Template(Sql.insert_driver).substitute(locals())
            cursor.execute(sql)
            environ['connection'].commit()
    for vehicle_id in form['vehicles']:
        if vehicle_id != '0':
            sql = Template(Sql.insert_vehicle).substitute(locals())
            cursor.execute(sql)
            environ['connection'].commit()
    output = 'Updated'
    return json_app(output)(environ, start_response)
